package com.homework.daoImpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.homework.bean.Student;
import com.homework.dao.ShowStudentsDao;
import com.homework.jdbc.DBConnection;

public class ShowStudentsDaoImpl implements ShowStudentsDao {
	public List<Student> getAllStudents() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<Student>();

		conn = DBConnection.getConnection();
		String sql = "select s.studentID, s.studentName, s.studentSex, s.studentClass,t.cnt  from db_student s left join (select studentID, count(studentID) cnt from db_dianming group by studentID) t on s.studentID = t.studentID order by s.studentID asc";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				String id = rs.getString("s.studentID");
				String name = rs.getString("s.studentName");
				String sex = rs.getString("s.studentSex");
				Integer count = rs.getInt("t.cnt");
				String studentClass = rs.getString("studentClass");

				Student stu = new Student();
				stu.setUserID(id);
				stu.setName(name);
				stu.setSex(sex);
				stu.setStudentClass(studentClass);
				stu.setCount(count);

				list.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}
